﻿/*
 *  This file is part of Worker Time.
 *
 *  Worker Time is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  Worker Time is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Worker Time.  If not, see <http://www.gnu.org/licenses/>.
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Data.Common;
using System.Windows.Forms;
using System.Globalization;
using System.ComponentModel;
using System.Collections;

namespace WorkerTime.Database
{
    public class Manager
    {
        public DbConnection sqlConnection;
        public DbCommand sqlCommand;
        public String fileName;
        private ComponentResourceManager localization;

        public Manager(String fileName)
        {
            localization = new System.ComponentModel.ComponentResourceManager(typeof(WorkerTimeStrings));
            this.fileName = fileName;
            if (File.Exists(fileName)) {
                this.sqlConnection = new SQLiteConnection("Data Source=" + fileName );
                this.sqlCommand = this.sqlConnection.CreateCommand();
                sqlConnection.Open();
            } else {
                this.sqlConnection = new SQLiteConnection("Data Source=" + fileName);
                this.sqlCommand = this.sqlConnection.CreateCommand();
                sqlConnection.Open();

                ExecuteNonQuery("CREATE TABLE Tracks ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [ProjectID] INTEGER, [ActivityID] INTEGER, [Start] DATETIME, [End] DATETIME, [Duration] INTEGER)");
                ExecuteNonQuery("CREATE TABLE Projects ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT)");
                ExecuteNonQuery("CREATE TABLE Activity ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT, [ProjectID] INTEGER)");
                
                if (MessageBox.Show(localization.GetString("String10"), localization.GetString("String3"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes )
                {
                    ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 1')");
                    ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 2')");
                    ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 3')");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 1',1)");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 2',1)");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 3',1)");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 1',2)");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 2',2)");
                    ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 3',2)");
                }
            }

            Update1();
        }

        private void Update1()
        {
            Boolean isToUpdate = false;
            DbDataReader reader = ExecuteReader("PRAGMA table_info(Tracks)");
            if (reader.HasRows)
            {
                while (reader.Read()) 
                {
                    if (((String)reader["Name"]).ToLower() == "DURATION".ToLower())
                        if (((String)reader["Type"]).ToLower() == "TIMESTAMP".ToLower())
                            isToUpdate = true;
                }
            }
            reader.Close();

            if (isToUpdate)
            {
                /*
                 * Converte o valor do campo
                 */
                Hashtable tracks = new Hashtable();
                reader = ExecuteReader("SELECT ID, Start, End FROM Tracks");
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        tracks.Add(reader["ID"], (TimeSpan)(((DateTime)reader["End"]) - ((DateTime)reader["Start"])));
                        
                    }
                }

                reader.Close();

                IDictionaryEnumerator Enumerator = tracks.GetEnumerator();

                while (Enumerator.MoveNext())
                {
                    ExecuteNonQuery("UPDATE Tracks SET Duration='" + ((TimeSpan) Enumerator.Value).Ticks + "' WHERE ID='" + Enumerator.Key.ToString() + "'"); 
                }

                /*
                 * Converte a estrutura da base de dados
                 */
                ExecuteNonQuery("ALTER TABLE \"main\".\"Tracks\" RENAME TO \"__temp__Tracks\"");
                ExecuteNonQuery("CREATE TABLE \"main\".\"Tracks\" (\"ID\" INTEGER PRIMARY KEY ,\"ProjectID\" INTEGER,\"ActivityID\" INTEGER,\"Start\" DATETIME,\"End\" DATETIME,\"Duration\" INTEGER)");
                ExecuteNonQuery("INSERT INTO \"main\".\"Tracks\" SELECT * FROM \"main\".\"__temp__Tracks\"");
                ExecuteNonQuery("DROP TABLE \"main\".\"__temp__Tracks\"");
            }
        }

        public void ExecuteNonQuery(string CommandText) 
        {
            
            sqlCommand.CommandText = CommandText;
            sqlCommand.ExecuteNonQuery();
        }

        public DbDataReader ExecuteReader(string CommandText)
        {
            sqlCommand.CommandText = CommandText;
            return sqlCommand.ExecuteReader();
        }

        public String GetProjectId(string name) 
        {
            String id;
            DbDataReader reader = ExecuteReader("SELECT ID FROM Projects WHERE Name='" + name + "'");
            reader.Read();
            if (reader.HasRows)
            {
                id = reader[0].ToString();                
            }
            else
            {
                id = "";
                throw new ApplicationException("Projeto incluida recentemente não encontrada na base de dados");
            }
            reader.Close();
            return (id);
        }

        public String GetActivityId(string name)
        {
            String id;
            DbDataReader reader = ExecuteReader("SELECT ID FROM Activity WHERE Name='" + name + "'");
            reader.Read();
            if (reader.HasRows)
            {
                id = reader[0].ToString();
            }
            else
            {
                id = "";
                throw new ApplicationException("Atividade incluida recentemente não encontrada na base de dados");
            }
            reader.Close();
            return (id);
        }

        public void Close()
        {
            sqlConnection.Close();
        }

        public long GetDaysTotal()
        {
            TimeSpan totalDuration = new TimeSpan();
            DbDataReader reader = ExecuteReader("SELECT Duration FROM Tracks WHERE Start Between '" + DateTime.Today.ToString("s", DateTimeFormatInfo.InvariantInfo) + "' AND '" + DateTime.Today.AddDays(1.0).ToString("s", DateTimeFormatInfo.InvariantInfo) + "'");
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    TimeSpan duration = new TimeSpan((long)reader[0]);
                    totalDuration += duration;
                }
            }
            reader.Close();
            return (totalDuration.Ticks);
        }

        public long GetProjectsTotal(String projectId)
        {
            TimeSpan totalDuration = new TimeSpan();
            DbDataReader reader = ExecuteReader("SELECT Duration FROM Tracks WHERE ProjectID = '"+projectId+"'");
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    TimeSpan duration = new TimeSpan((long)reader[0]);
                    totalDuration += duration;
                }
            }
            reader.Close();
            return (totalDuration.Ticks);
        }

        public long GetActivitysTotal(String activityId)
        {
            TimeSpan totalDuration = new TimeSpan();
            DbDataReader reader = ExecuteReader("SELECT Duration FROM Tracks WHERE ActivityID = '" + activityId + "'");
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    TimeSpan duration = new TimeSpan((long)reader[0]);
                    totalDuration += duration;
                }
            }
            reader.Close();
            return (totalDuration.Ticks);
        }
    }
}
